# _*_ coding:utf-8 _*_
# @File  : warehouse.py
# @Time  : 2021-09-16  08:47
# @Author: zizle

from fastapi import APIRouter, Path, Query, Body
from fastapi.encoders import jsonable_encoder
from pydantic import BaseModel
from db import FAConnection
from db import utils as dbutils
from status import r_status
from category import WAREHOUSE_TYPE

warehouse_api = APIRouter()


@warehouse_api.get('/', summary='分页获取仓库')
async def delivery_warehouse_paginator(page: int = Query(..., ge=1), page_size: int = Query(..., ge=10, le=200)):
    db = FAConnection(conn_name='admin分页查仓库')
    start, offset = (page - 1) * page_size, page_size
    sql = 'SELECT SQL_CALC_FOUND_ROWS id,create_time,house_name,short_name,province,longitude,latitude,type,address,' \
          'arrived,is_active ' \
          'FROM info_delivery_warehouse ORDER BY create_time DESC LIMIT %s,%s;'
    records = db.query(sql, [start, offset], keep_conn=True)
    total = db.query('SELECT FOUND_ROWS() AS total;', fetchone=True)[0]
    total_count = total['total'] if total else 1
    for item in records:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['type_text'] = WAREHOUSE_TYPE.get(item['type'], '未知')
    return {'code': r_status.SUCCESS, 'message': '查询成功!', 'data': records, 'total_count': total_count,
            'page': page, 'page_size': page_size}


class WarehouseItem(BaseModel):
    house_name: str
    short_name: str = ''
    province: str
    longitude: float
    latitude: float
    type: int
    address: str
    arrived: str = ''
    is_active: int


@warehouse_api.post('/', summary='新建一个仓库')
async def create_warehouse(warehouse_item: WarehouseItem = Body(...)):
    sql = 'INSERT INTO info_delivery_warehouse (house_name,short_name,province,longitude,latitude,type,' \
          'address,arrived) ' \
          'VALUES (%(house_name)s,%(short_name)s,%(province)s,%(longitude)s,%(latitude)s,%(type)s,' \
          '%(address)s,%(arrived)s);'
    db = FAConnection(conn_name='新增仓库')
    if warehouse_item.short_name:  # short_name不为空时查询时否已经存在，存在不能再添加
        query_exists = db.query('SELECT id,short_name FROM info_delivery_warehouse WHERE short_name=%s LIMIT 1;',
                                param=[warehouse_item.short_name], fetchone=True, keep_conn=True)[0]
        if query_exists:
            db.close()
            return {'code': r_status.CREATED_SUCCESS, 'message': '仓库简称已经存在，无需重复添加!', 'data': {'id': query_exists['id']}}
    warehouse_json = jsonable_encoder(warehouse_item)

    count, new_id = db.insert(sql, param=warehouse_json)
    if count:
        return {'code': r_status.CREATED_SUCCESS, 'message': '创建仓库成功!', 'data': {'id': new_id}}
    return {'code': r_status.UNKNOWN_ERROR, 'message': '服务器错误,添加失败!', 'data': {'id': -1}}


@warehouse_api.get('/query/', summary='关键字搜索名称或简称')
async def query_warehouse(kw: str = Query(...)):
    sql = 'SELECT id,create_time,house_name,short_name,province,longitude,latitude,type,address,' \
          'arrived,is_active ' \
          'FROM info_delivery_warehouse ' \
          'WHERE house_name LIKE %s OR short_name LIKE %s;'
    db = FAConnection(conn_name='搜索简称')
    records = db.query(sql, param=['%{}%'.format(kw), '%{}%'.format(kw)])
    for item in records:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['type_text'] = WAREHOUSE_TYPE.get(item['type'], '未知')
    return {'code': r_status.SUCCESS, 'message': '搜索仓库成功!', 'data': records}


@warehouse_api.get('/info/', summary='根据ID获取仓库信息')
async def get_warehouse_by_id(wid: int = Query(..., ge=1)):
    db = FAConnection()
    sql = 'SELECT id,create_time,house_name,short_name,province,longitude,latitude,type,address,' \
          'arrived,is_active ' \
          'FROM info_delivery_warehouse WHERE id=%s LIMIT 1;'
    warehouse = db.query(sql, param=[wid], fetchone=True)[0]
    if warehouse:
        return {'code': r_status.SUCCESS, 'message': '查询仓库信息成功!', 'data': warehouse}
    return {'code': r_status.NOT_CONTENT, 'message': '仓库不存在!', 'data': {}}


@warehouse_api.put('/info/{warehouse_id}/', summary='根据ID更新仓库信息')
async def modify_warehouse_by_id(warehouse_id: int = Path(..., ge=1), warehouse_item: WarehouseItem = Body(...)):
    warehouse_json = jsonable_encoder(warehouse_item)
    middle_sql = dbutils.create_sql(warehouse_json)
    update_sql = 'UPDATE info_delivery_warehouse SET ' + middle_sql + 'WHERE id=%(id)s LIMIT 1;'
    warehouse_json['id'] = warehouse_id
    db = FAConnection()
    _, success = db.execute(update_sql, param=warehouse_json)
    if success:
        return {'code': r_status.SUCCESS, 'message': '更新仓库信息成功!', 'data': warehouse_json}
    return {'code': r_status.VALIDATE_ERROR, 'message': '仓库信息更新失败!', 'data': {}}


class DeliveryVarietyItem(BaseModel):
    warehouse_id: int
    variety_en: str
    linkman: str
    links: str
    premium: str
    receipt_unit: str
    is_active: int


@warehouse_api.post('/variety/', summary='给一个仓库增加交割品种')
async def create_delivery_variety(delivery_item: DeliveryVarietyItem = Body(...)):
    delivery_json = jsonable_encoder(delivery_item)
    sql = 'INSERT INTO info_warehouse_variety (warehouse_id,variety_en,linkman,links,premium,receipt_unit,is_active) ' \
          'VALUES (%(warehouse_id)s,%(variety_en)s,%(linkman)s,%(links)s,%(premium)s,%(receipt_unit)s,%(is_active)s);'

    db = FAConnection(conn_name='仓库创建交割品种')
    _, success = db.insert(sql, param=delivery_json)
    if success:
        return {'code': r_status.CREATED_SUCCESS, 'message': '创建交割品种成功!'}
    return {'code': r_status.UNKNOWN_ERROR, 'message': '添加失败，服务器错误!'}


# 获取一个仓库的交割品种
@warehouse_api.get('/variety/', summary='获取一个仓库的交割品种')
async def get_delivery_variety(wid: int = Query(..., ge=1)):
    sql = 'SELECT a.id,a.warehouse_id,a.variety_en,b.variety_name,a.linkman,a.links,a.premium,a.receipt_unit,' \
          'a.is_active FROM ' \
          'info_warehouse_variety AS a ' \
          'INNER JOIN sys_variety AS b ON a.variety_en=b.variety_en ' \
          'WHERE a.warehouse_id=%s;'
    db = FAConnection()
    records = db.query(sql, param=[wid])
    return {'code': r_status.SUCCESS, 'message': '查询仓库交割品种信息成功!', 'data': records}


# 修改一个仓库的某个交割品种信息
@warehouse_api.put('/variety/{did}/', summary='修改一个仓库的某个交割品种信息')
async def modify_delivery_variety(did: int = Path(..., ge=1), body_item: DeliveryVarietyItem = Body(...)):
    body_json = jsonable_encoder(body_item)
    # 删除warehouse_id字段，本字段不可修改
    if 'warehouse_id' in body_json.keys():
        del body_json['warehouse_id']
    middle_sql = dbutils.create_sql(body_json)
    sql = 'UPDATE info_warehouse_variety SET ' + middle_sql + 'WHERE id=%(id)s LIMIT 1;'
    body_json['id'] = did
    db = FAConnection()
    _, s = db.execute(sql, param=body_json)
    if s:
        return {'code': r_status.SUCCESS, 'message': '修改交割品种信息成功!'}
    return {'code': r_status.VALIDATE_ERROR, 'message': '修改信息失败,请检查数据是否有误!'}


# 获取一个品种的所有仓库
@warehouse_api.get('/{variety_en}/', summary='获取一个品种的相关交割仓库')
async def get_variety_warehouse(variety_en: str = Path(...)):
    sql = "SELECT a.id,a.create_time,a.house_name,a.short_name,a.province,a.longitude,a.latitude,a.arrived,a.is_active," \
          "a.type,a.address " \
          "FROM info_delivery_warehouse AS a " \
          "INNER JOIN info_warehouse_variety AS b ON a.id=b.warehouse_id " \
          "WHERE b.variety_en=%s AND b.is_active=1;"
    db = FAConnection()
    records = db.query(sql, param=[variety_en])
    for item in records:
        item['create_time'] = item['create_time'].strftime('%Y-%m-%d %H:%M:%S')
        item['type_text'] = WAREHOUSE_TYPE.get(item['type'], '未知')
    return {'code': r_status.SUCCESS, 'message': '获取品种的交割库成功!', 'data': records}
